package server;

import girafPlace.lib.State;
import girafPlace.lib.UserProfile;
import girafPlace.lib.Application;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * A class for managing the connection from the server to the database. Provided
 * the static getApplications(userProfile) method, for getting the applications
 * from the database corresponding to the given profile
 * 
 * @author sw6c
 * 
 */
public class SqlManager {
	private static Connection connection;

	/**
	 * Creates connection to the database and stores the connection in the class
	 * variable connection for retrieval.
	 * 
	 * @throws ClassNotFoundException
	 *             If driver class was not found
	 * @throws SQLException
	 *             If the database connection could not be established
	 */
	private static void createConnection() throws ClassNotFoundException,
			SQLException {
		Class.forName("com.mysql.jdbc.Driver");

		String url = "jdbc:mysql://home.lcdev.dk:3306/girafplace"; // a JDBC url
		String username = "rune";
		String password = "kodeord";

		try {
			connection = DriverManager.getConnection(url, username, password);
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
	}

	/**
	 * Fetch the connection, if none exists, create it, and store it in the
	 * protected static connection variable
	 * 
	 * @throws ClassNotFoundException
	 *             If the driver class is not found.
	 * @throws SQLException
	 *             If the is a problem with the SQL.
	 */
	private static void getConnection() throws ClassNotFoundException,
			SQLException {
		if (connection == null || !connection.isValid(0)) {
			createConnection();
		}
	}

	/**
	 * Gets the applications from the database, that should be available for the
	 * provided UserProfile
	 * 
	 * @param user
	 *            The UserProfile that should be used to filter the applications
	 * @return The applications from the database that the UserProfile is
	 *         capable of using.
	 * @throws SQLException
	 *             If there is a problem with the SQL.
	 * @throws ClassNotFoundException
	 *             If the driver class is not found.
	 */
	public static List<Application> getApplications(UserProfile user)
			throws SQLException, ClassNotFoundException {
		String query = "SELECT * FROM applications WHERE ";

		if (!user.canDragAndDrop()) {
			query += " canDragAndDrop = 0 AND";
		}

		if (!user.canHear()) {
			query += " canHear = 0 AND";
		}

		if (!user.requiresSimpleVisualEffects()) {
			query += " requiresSimpleVisualEffects = 0 AND";
		}

		if (!user.canAnalogTime()) {
			query += " canAnalogTime = 0 AND";
		}

		if (!user.canDigitalTime()) {
			query += " canDigitalTime = 0 AND";
		}

		if (!user.canRead()) {
			query += " canRead = 0 AND";
		}

		if (!user.hasBadVision()) {
			query += " hasBadVision = 0 AND";
		}

		if (!user.requiresLargeButtons()) {
			query += " requiresLargeButtons = 0 AND";
		}

		if (!user.canSpeak()) {
			query += " canSpeak = 0 AND";
		}

		if (!user.canNumbers()) {
			query += " canNumbers = 0 AND";
		}

		if (!user.canUseKeyboard()) {
			query += " canUseKeyboard = 0 AND";
		}

		query += " state='LIVE'";

		getConnection();
		Statement statement = connection.createStatement();

		List<Application> returnValue = new ArrayList<Application>();

		if (statement.execute(query)) {
			ResultSet result = statement.getResultSet();
			result.beforeFirst();

			while (result.next()) {
				final int id = result.getInt(1);
				final String name = result.getString(2);
				final String description = result.getString(3);
				final String package_ = result.getString(5);
				final int version = result.getInt(6);
				final String versionString = result.getString(7);
				final State state = State.LIVE;
				final boolean canRead = result.getBoolean(9);
				final boolean canDragAndDrop = result.getBoolean(10);
				final boolean canHear = result.getBoolean(11);
				final boolean requiresSimpleVisualEffects = result
						.getBoolean(12);
				final boolean canAnalogTime = result.getBoolean(13);
				final boolean canDigitalTime = result.getBoolean(14);
				final boolean hasBadVision = result.getBoolean(15);
				final boolean requiresLargeButtons = result.getBoolean(16);
				final boolean canSpeak = result.getBoolean(17);
				final boolean canNumbers = result.getBoolean(18);
				final boolean canUseKeyboard = result.getBoolean(19);

				Application app = new Application(id, name, description,
						package_, version, versionString, state);

				app.setCanRead(canRead);
				app.setCanDragAndDrop(canDragAndDrop);
				app.setCanHear(canHear);
				app.setRequiresSimpleVisualEffects(requiresSimpleVisualEffects);
				app.setCanAnalogTime(canAnalogTime);
				app.setCanDigitalTime(canDigitalTime);
				app.setHasBadVision(hasBadVision);
				app.setRequiresLargeButtons(requiresLargeButtons);
				app.setCanSpeak(canSpeak);
				app.setCanNumbers(canNumbers);
				app.setCanUseKeyboard(canUseKeyboard);

				returnValue.add(app);
			}
			return returnValue;
		} else
			return null;
	}
}
